var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
/* GET users listing. */
router.get('/', function(req, res, next) {
    var db=new sqlite3.Database(path.join(__dirname, "../db/LH.s3db"));
    if(req.query.status==2){//显示所有未参加的活动
        try{
            if(req.query.manager!=undefined)
            {
                sqlStr1 = "SELECT * FROM activity WHERE a_no not in (SELECT a_no FROM s_activity where s_id = ?) and m_id = ? and status=1";
                para = [req.session.username,req.query.manager];
            }else{
                sqlStr1 = "SELECT * FROM activity WHERE a_no not in (SELECT a_no FROM s_activity where s_id = ?) and status=1";
                para = [req.session.username];
            }
            db.all(sqlStr1,para ,function(err,rows){
                if(rows.length==0){
                    res.send('0');
                }else{
                    table={}
                    table.data=rows;
                    table.count=rows.length;
                    res.status(200).json(table);
                }
            } );
        }catch(ex){
            console.log(ex);
        }
    }
    else if(req.query.status==1)//显示所有已提交材料的活动
    {
        try{
            sqlStr1 = "SELECT * FROM activity WHERE a_no in (SELECT a_no FROM s_activity where s_id = ? and status <> 0)";
            db.all(sqlStr1, [req.session.username],function(err,rows){
                if(rows.length==0){
                    res.send('0');
                }else{
                    table={}
                    table.data=rows;
                    table.count=rows.length;
                    res.status(200).json(table);
                }
            } );
        }catch(ex){
            console.log(ex);
        }
    }else if(req.query.status==0)//显示所有未提交材料的活动
    {
        try{
            sqlStr1 = "SELECT * FROM activity WHERE a_no in (SELECT a_no FROM s_activity where s_id = ? and status = 0)";
            db.all(sqlStr1, [req.session.username],function(err,rows){
                if(rows.length==0){
                    res.send('0');
                }else{
                    table={}
                    table.data=rows;
                    table.count=rows.length;
                    res.status(200).json(table);
                }
            } );
        }catch(ex){
            console.log(ex);
        }
    }
 else if(req.query.status==3)//显示所搜活动
    {
        try{
            sqlStr1 = "SELECT * FROM activity WHERE a_name=?";
            db.all(sqlStr1, [req.query.aname],function(err,rows){
                if(rows.length==0){
                    res.send('0');
                }else{
                    table={}
                    table.data=rows;
                    console.log(table.data);
                    table.count=rows.length;
                    res.status(200).json(table);
                }
            } );
        }catch(ex){
            console.log(ex);
        }
    }
    else if (req.query.status == 4) //显示所有活动
    {
        try {
            sqlStr1 = "SELECT * FROM activity where m_id=?";
            db.all(sqlStr1, "1001", function (err, rows) {
                table = {}
                table.data = rows;
                console.log(table.data);
                table.count1all = rows.length;
                table.count11 = 0;
                table.count12 = 0;
                table.count13 = 0;
                table.count14 = 0;
                if (table.data) {
                    i = 0;
                    for (i in table.data) {
                        if (table.data[i].status == 1) {
                            table.count11 = table.count11 + 1;
                        } else if (table.data[i].status == 2) {
                            table.count12 = table.count12 + 1;
                        } else if (table.data[i].status == -1) {
                            table.count13 = table.count13 + 1;
                        } else if (table.data[i].status == 0) {
                            table.count14 = table.count14 + 1;
                        }
                    }
                }
                db.all(sqlStr1, "1002", function (err, rows) {
                    table.data = rows;
                    console.log(table.data);
                    table.count2all = rows.length;
                    table.count21 = 0;
                    table.count22 = 0;
                    table.count23 = 0;
                    table.count24 = 0;
                    if (table.data) {
                        i = 0;
                        for (i in table.data) {
                            if (table.data[i].status == 1) {
                                table.count21 = table.count21 + 1;
                            } else if (table.data[i].status == 2) {
                                table.count22 = table.count22 + 1;
                            } else if (table.data[i].status == -1) {
                                table.count23 = table.count23 + 1;
                            } else if (table.data[i].status == 0) {
                                table.count24 = table.count24 + 1;
                            }
                        }
                    }
                    db.all(sqlStr1, "1003", function (err, rows) {
                        table.data = rows;
                        console.log(table.data);
                        table.count3all = rows.length;
                        table.count31 = 0;
                        table.count32 = 0;
                        table.count33 = 0;
                        table.count34 = 0;
                        i = 0;
                        if (table.data) {
                            for (i in table.data) {
                                if (table.data[i].status == 1) {
                                    table.count31 = table.count31 + 1;
                                } else if (table.data[i].status == 2) {
                                    table.count32 = table.count32 + 1;
                                } else if (table.data[i].status == -1) {
                                    table.count33 = table.count33 + 1;
                                } else if (table.data[i].status == 0) {
                                    table.count34 = table.count34 + 1;
                                }

                            }
                        }
                        db.all(sqlStr1, "1004", function (err, rows) {
                            table.data = rows;
                            console.log(table.data);
                            table.count4all = rows.length;
                            table.count41 = 0;
                            table.count42 = 0;
                            table.count43 = 0;
                            table.count44 = 0;
                            if (table.data) {
                                i = 0;
                                for (i in table.data) {
                                    if (table.data[i].status == 1) {
                                        table.count41 = table.count41 + 1;
                                    } else if (table.data[i].status == 2) {
                                        table.count42 = table.count42 + 1;
                                    } else if (table.data[i].status == -1) {
                                        table.count43 = table.count43 + 1;
                                    } else if (table.data[i].status == 0) {
                                        table.count44 = table.count44 + 1;
                                    }

                                }
                            }
                            res.status(200).json(table);
                        });
                    });
                });
            });
        } catch (ex) {
            console.log(ex);
        }
    }
    else if(req.query.status==5)//显示所搜活动
    {
        try{
            sqlStr1 = "SELECT * FROM activity join manager on activity.[m_id]=manager.[m_id] WHERE activity.m_id=? and status=1";
            db.all(sqlStr1, [req.query.mid],function(err,rows){
                if(rows.length==0){
                    res.send('0');
                }else{
                    table={}
                    table.data=rows;
                    console.log(table.data);
                    table.count=rows.length;
                    res.status(200).json(table);
                }
            } );
        }catch(ex){
            console.log(ex);
        }
    }
    else if(req.query.status==6)//显示其他组织活动
    {
        try{
            sqlStr1 = "SELECT * FROM activity join manager on activity.[m_id]=manager.[m_id] WHERE activity.m_id=? and status=1";
            db.all(sqlStr1, [req.session.username],function(err,rows){
                if(rows.length==0){
                    res.send('0');
                }else{
                    table={}
                    table.data=rows;
                    console.log(table.data);
                    table.count=rows.length;
                    res.status(200).json(table);
                }
            } );
        }catch(ex){
            console.log(ex);
        }
    }
    else if (req.query.status == 7) //显示所有活动
    {
        try {
            sqlStr1 = "SELECT * FROM activity join manager on activity.[m_id]=manager.[m_id] WHERE activity.m_id=?";
            db.all(sqlStr1, [req.session.username], function (err, rows) {
                table = {}
                table.data = rows;
                console.log(table.data);
                table.count1all = rows.length;
                table.count11 = 0;
                table.count12 = 0;
                table.count13 = 0;
                table.count14 = 0;
                if (table.data) {
                    i = 0;
                    for (i in table.data) {
                        if (table.data[i].status == 1) {
                            table.count11 = table.count11 + 1;
                        } else if (table.data[i].status == 2) {
                            table.count12 = table.count12 + 1;
                        } else if (table.data[i].status == -1) {
                            table.count13 = table.count13 + 1;
                        } else if (table.data[i].status == 0) {
                            table.count14 = table.count14 + 1;
                        }
                    }
                    table.data=rows;
                    console.log(table.data);
                    table.count=rows.length;
                    res.status(200).json(table);
                }
            });
        } catch (ex) {
            console.log(ex);
        }
    }
    
});

module.exports = router;
